package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.LinkedList;

import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.AuctionLogDao;
import com.ws.shop.entity.AuctionLog;
import com.ws.shop.util.JdbcUtil;

@Repository(value="auctionlogDao")
public class AuctionLogDaoImpl  extends BaseDaoImpl  implements AuctionLogDao {
	private static Logger log = Logger.getLogger(AuctionLogDaoImpl.class);

    ////获取需要补差价数据
	public Map mycount(final long aid,final long uid){
		String s = "SELECT   SUM(beat) AS self_point_a,SUM(pointb) AS self_point_b,COUNT(1) AS self_bidcount FROM  jvf_auction_log WHERE aid = "+aid+" AND uid = "+uid;
		  return(Map)this.query(s, new ResultSetExtractor<Map>(){
				public Map extractData(ResultSet rs) throws SQLException,
						DataAccessException {
				    Map map = new HashMap();
					while(rs.next()){
					   map.put("sumbeat", rs.getInt(1));
					   map.put("sumpoint", rs.getInt(2));
					   map.put("sumpointb", rs.getInt(3));
					}
					return map;
				}
				
		  });	
	}

	final String findAuctionLogByPage_count = "select count(1) from jvf_auction_log";
	final String findAuctionLogByPage_list = "select id,aid,uid,name,goodsname,beat,pointb,ip,address,bidprice,addtime from jvf_auction_log";
	final String findAuctionLogByPage_order = " order by id desc";
	public Page findAuctionLogByPage(int pageno, int pagesize, String start,
			String end, String name, int aid) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(name != null){
			query.append(" and name='").append(name).append("'");
		}
		if(aid > 0){
			query.append(" and aid=").append(aid);
		}
		query.append(" and addtime<='").append(end).append(" 23:59:59'")
			.append(" and addtime>='").append(start).append(" 00:00:00'");
		return this.findPage(pageno, pagesize, new StringBuilder(findAuctionLogByPage_count).append(query).toString(),
				new StringBuilder(findAuctionLogByPage_list).append(query).append(findAuctionLogByPage_order));
	}
	
	public void getAuctionLogByUid(final Page page,final long uid){
		 this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							PreparedStatement psmt = null;
							ResultSet rs = null;
							try {
								String s = "SELECT COUNT(1) FROM jvf_auction_log WHERE uid = ?";
								psmt = connect.prepareStatement(s);
								psmt.setLong(1, uid);
								rs = psmt.executeQuery();
								long totalnum = 0;
								if(rs.next()){
									totalnum = rs.getLong(1);
								}
								JdbcUtil.close(rs,psmt,null);
								if(totalnum!=0){
									StringBuffer bufer = new StringBuffer();
                                 bufer.append("SELECT uid,name,ip,address,bidprice,addtime FROM jvf_auction_log WHERE uid = ? ORDER BY  addtime ");
									bufer.append(" limit ?, ? ").toString();
									psmt = connect.prepareStatement(s);
									psmt.setLong(1, uid);
									psmt.setLong(2, page.getStartrownum());
									psmt.setInt(3, page.getPagesize());
									rs = psmt.executeQuery();
									//System.out.println(sb.toString());
									ResultSetMetaData rsm = rs.getMetaData();
									// 取得数据库的列数
									int col = rsm.getColumnCount();
									// 生成col长度的Object数组
									Object[] obj = new Object[col];
									// 遍历结果集，将结果存入Object数组
									while (rs.next()) {
										for (int i = 0; i < col; i++) {
											obj[i] = rs.getObject(i + 1);
										}
										page.addData(obj);
									}
								}
								return null;
							} catch (Exception e) {
								e.printStackTrace();
								log.error("getAuctionLogByUid !",e);
								return null;
							} finally {
								JdbcUtil.close(rs, psmt, null);
				      		}
				}
			});
	}
	
	
	public void getAuctionLogByUidAndAid(final Page page,final long uid,final long aid){
		 this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							PreparedStatement psmt = null;
							ResultSet rs = null;
							try {
								String s = "SELECT COUNT(1) FROM jvf_auction_log WHERE uid = ? and aid = ?";
								psmt = connect.prepareStatement(s);
								psmt.setLong(1, uid);
								psmt.setLong(2, aid);
								rs = psmt.executeQuery();
								long totalnum = 0;
								if(rs.next()){
									totalnum = rs.getLong(1);
								}
								JdbcUtil.close(rs,psmt,null);
								if(totalnum!=0){
									StringBuffer bufer = new StringBuffer();
                                bufer.append("SELECT uid,name,ip,address,bidprice,addtime FROM jvf_auction_log WHERE uid = ? and aid = ? ORDER BY  addtime ");
									bufer.append(" limit ?, ? ").toString();
									psmt = connect.prepareStatement(s);
									psmt.setLong(1, uid);
									psmt.setLong(2, aid);
									psmt.setLong(3, page.getStartrownum());
									psmt.setInt(4, page.getPagesize());
									rs = psmt.executeQuery();
									//System.out.println(sb.toString());
									ResultSetMetaData rsm = rs.getMetaData();
									// 取得数据库的列数
									int col = rsm.getColumnCount();
									// 生成col长度的Object数组
									Object[] obj = new Object[col];
									// 遍历结果集，将结果存入Object数组
									while (rs.next()) {
										for (int i = 0; i < col; i++) {
											obj[i] = rs.getObject(i + 1);
										}
										page.addData(obj);
									}
								}
								return null;
							} catch (Exception e) {
								e.printStackTrace();
								log.error(" getAuctionLogByUidAndAid!",e);
								return null;
							} finally {
								JdbcUtil.close(rs, psmt, null);
				      		}
				}
			});
	}
    
	public List<AuctionLog> getLogData(final long aid){
		final String s = "select id,aid,uid,name,goodsname,beat,pointb,ip,address,bidprice,addtime from jvf_auction_log where aid = ? order by id desc limit 10";
		return (List)this.execute(
				new ConnectionCallback() {
					public Object doInConnection(Connection connect) {
						PreparedStatement psmt = null;
						ResultSet rs = null;
						try {
							List<AuctionLog> auctionLog = new LinkedList<AuctionLog>();
							psmt = connect.prepareStatement(s);
							psmt.setLong(1, aid);
							rs = psmt.executeQuery();
							while(rs.next()){
								AuctionLog alog = getData(rs);
								auctionLog.add(alog);
							}
							return auctionLog;
						} catch (Exception e) {
							e.printStackTrace();
							log.error(" getLogData !",e);
							return null;
						} finally {
							JdbcUtil.close(rs, psmt, null);
			      		}
			}
		});
	}
	
	
	
	public AuctionLog getData(ResultSet rs) throws SQLException{
		AuctionLog alog = new AuctionLog();
		alog.setId(rs.getLong(1));
		alog.setAid(rs.getLong(2));
		alog.setUid(rs.getInt(3));
		alog.setName(rs.getString(4));
		alog.setGoodsname(rs.getString(5));
		alog.setBeat(rs.getInt(6));
		alog.setPointb(rs.getInt(7));
		alog.setIp(rs.getString(8));
		alog.setAddress(rs.getString(9));
		alog.setBidprice(rs.getFloat(10));
		alog.setAddtime(rs.getTimestamp(11));
		return alog;
	}
	
	final String findAuctionLogByPage1_count = "select count(1) from jvf_auction_log";
	final String findAuctionLogByPage1_list = "select id,aid,uid,name,goodsname,beat,pointb,ip,address,bidprice,addtime from jvf_auction_log";
	final String findAuctionLogByPage1_order = " order by bidprice desc";
	public Page findAuctionLogByPage(int pageno, int pagesize, long aid) {
		StringBuilder query = new StringBuilder(" where aid=").append(aid);
		return this.findPage(pageno, pagesize, new StringBuilder(findAuctionLogByPage1_count).append(query).toString(),
				new StringBuilder(findAuctionLogByPage1_list).append(query).append(findAuctionLogByPage1_order));
	}

	final String findAuctionLog_list = "select id,aid,uid,name,goodsname,beat,pointb,ip,address,bidprice,addtime from jvf_auction_log";
	final String findAuctionLog_order = " order by aid,bidprice desc";
	public List<Map<String,Object>> findAuctionLogList(int startrow, int size, long aid) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(aid > 0){
			query.append(" and aid=").append(aid);
		}
		return findList(startrow,size,new StringBuilder(findAuctionLog_list).append(query).append(findAuctionLog_order));
	}
	
}
